var express = require('express');
var router = express.Router();
var {
  sequelize,
  User,
  Project
} = require('../models');

var Sequelize = require('sequelize');


router.get('/roads/all', function (req, res, next) {
  //查询表名称为road的所有数据
  sequelize.query('select * from roads').then(function (data) {
    //转换成geojson格式
    let geojson = {
      "type": "FeatureCollection",
      "crs": data[1].rows[0].geom.crs,
      "features": []
    };
    data[1].rows.forEach(element => {
      let feature = {
        "type": "Feature",
        "properties": {},
        "geometry": {

        }
      };
      //properties不包括geom
      for (let key in element) {
        if (key != 'geom') {
          feature.properties[key] = element[key];
        }
      }
      //geom不包括crs
      delete element.geom.crs;
      feature.geometry = element.geom;
      geojson.features.push(feature);
    });

    res.json(geojson);


  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});
//查询表名称为road的管养单位为“query.unit”的数据
router.get('/roads/:unit', function (req, res, next) {
  sequelize.query('select * from roads where "管养单位"=\'' + req.params.unit + '\'').then(function (data) {
    let geojson = {
      "type": "FeatureCollection",
      "crs": data[1].rows[0].geom.crs,
      "features": []
    };
    data[1].rows.forEach(element => {
      let feature = {
        "type": "Feature",
        "properties": {},
        "geometry": {

        }
      };
      for (let key in element) {
        if (key != 'geom') {
          feature.properties[key] = element[key];
        }
      }
      delete element.geom.crs;
      feature.geometry = element.geom;
      geojson.features.push(feature);
    });
    res.json(geojson);
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});


//用户登录
router.post('/login', function (req, res, next) {
  User.findOne({
    where: {
      username: req.body.username,
      password: req.body.password
    }
  }).then(function (data) {
    if (data) {
      res.json({
        code: 200,
        data: data
      });
    } else {
      res.json({
        code: 400,
        data: '用户名或密码错误'
      });
    }
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});
//增用户
router.post('/user/add', function (req, res, next) {
  User.create({
    name: req.body.name,
    role: req.body.role,
    username: req.body.username,
    password: req.body.password,
  }).then(function (data) {
    res.json({
      code: 200,
      data: data
    });
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});
//删用户
router.post('/user/delete', function (req, res, next) {
  User.destroy({
    where: {
      id: req.body.id
    }
  }).then(function (data) {
    res.json({
      code: 200,
      data: data
    });
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});
//获取所有用户
router.get('/user/list', function (req, res, next) {
  User.findAll().then(function (data) {
    res.json({
      code: 200,
      data: data
    });
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});

//增项目
router.post('/project/add', function (req, res, next) {
  Project.create({
    unit: req.body.unit,
    route: req.body.route,
    imgname: req.body.imgname,
    longitude: req.body.longitude,
    latitude: req.body.latitude,
    time: req.body.time,
    damage: req.body.damage,
    imgpath: req.body.imgpath,
    isdispose: req.body.isdispose,
    gemo: req.body.gemo, // 假设gemo以GeoJSON格式提供
    userId: req.body.userId,
  }).then(function (data) {
    res.json({
      code: 200,
      data: data
    });
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});

//批量增项目
router.post('/project/addBatch', function (req, res, next) {
  let projects = req.body.projects;
  Project.bulkCreate(projects).then(function (data) {
    res.json({
      code: 200,
      data: data
    });
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});

//删项目
router.post('/project/delete', function (req, res, next) {
  Project.findOne({
    where: {
      id: req.body.id
    }
  }).then(function (data) {
    if (data) {
      data.destroy().then(async function (data1) {
        //删除图片
        await new Promise((resolve, reject) => {
            fs.unlink(data.imgpath, function (err) {
              if (err) {
                resolve(err);
              } else {
                resolve();
              }
            });
          }

        )
        res.json({
          code: 200,
          data: data
        });
      }).catch(function (err) {
        console.log(err);
        res.json(err);
      });
    } else {
      res.json({
        code: 400,
        data: '项目不存在'
      });
    }
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});

//改项目的isdispose
router.post('/project/updateDeal', function (req, res, next) {
  Project.update({
    isdispose: true,
    disposeNote: req.body.disposeNote
  }, {
    where: {
      id: req.body.id
    }
  }).then(function (data) {
    res.json({
      code: 200,
      data: data
    });
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});

//撤销isdispose
router.post('/project/revokeDeal', function (req, res, next) {
  Project.update({
    isdispose: false,
    disposeNote: null
  }, {
    where: {
      id: req.body.id
    }
  }).then(function (data) {
    res.json({
      code: 200,
      data: data
    });
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});



//获取所有项目
router.get('/project/all', function (req, res, next) {
  Project.findAll().then(function (data) {
    res.json({
      code: 200,
      data: data
    });
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});

//根据userId获取项目
router.get('/project/list/:userId', function (req, res, next) {
  Project.findAll({
    where: {
      userId: req.params.userId
    }
  }).then(function (data) {
    res.json({
      code: 200,
      data: data
    });
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});
//根据unit获取项目
router.get('/project/list/unit/:unit', function (req, res, next) {
  Project.findAll({
    where: {
      unit: req.params.unit
    }
  }).then(function (data) {
    res.json({
      code: 200,
      data: data
    });
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});


//根据userId获取项目,返回geojson
router.get('/project/list/geojson/:userId', function (req, res, next) {
  Project.findAll({
    where: {
      userId: req.params.userId
    }
  }).then(function (data) {
    let geojson = {
      "type": "FeatureCollection",
      "features": data.map(element => ({
        "type": "Feature",
        "properties": {
          "管养单位": element.unit,
          "路线编号": element.route,
          "图像名称": element.imgname,
          "拍摄时间": element.time,
          "损坏类型": element.damage,
          "图片路径": element.imgpath,
          "是否处置": element.isdispose ? '已处置' : '未处置',
          "处置备注": element.disposeNote,
          "经度": element.longitude,
          "纬度": element.latitude,
          '处理时间': element.updatedAt

        },
        "geometry": {
          "type": "Point",
          "coordinates": [element.longitude, element.latitude]
        }
      }))
    };
    res.json(geojson);
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});

//获取所有项目,返回geojson
router.get('/project/all/geojson', function (req, res, next) {
  Project.findAll().then(function (data) {
    let geojson = {
      "type": "FeatureCollection",
      "features": data.map(element => ({
        "type": "Feature",
        "properties": {
          // 这里添加每个项目的属性
          "管养单位": element.unit,
          "路线编号": element.route,
          "图像名称": element.imgname,
          "拍摄时间": element.time,
          "损坏类型": element.damage,
          "图片路径": element.imgpath,
          "是否处置": element.isdispose ? '已处置' : '未处置',
          "处置备注": element.disposeNote,
          "经度": element.longitude,
          "纬度": element.latitude,
          '处理时间': element.updatedAt


        },
        "geometry": {
          "type": "Point",
          "coordinates": [element.longitude, element.latitude]
        }
      }))
    };
    res.json(geojson);
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});

//根据unit获取项目,返回geojson
router.get('/project/list/unit/geojson/:unit', function (req, res, next) {
  Project.findAll({
    where: {
      unit: req.params.unit
    }
  }).then(function (data) {
    let geojson = {
      "type": "FeatureCollection",
      "features": data.map(element => ({
        "type": "Feature",
        "properties": {
          "管养单位": element.unit,
          "路线编号": element.route,
          "图像名称": element.imgname,
          "拍摄时间": element.time,
          "损坏类型": element.damage,
          "图片路径": element.imgpath,
          "是否处置": element.isdispose ? '已处置' : '未处置',
          "处置备注": element.disposeNote,
          "经度": element.longitude,
          "纬度": element.latitude,
          '处理时间': element.updatedAt

        },
        "geometry": {
          "type": "Point",
          "coordinates": [element.longitude, element.latitude]
        }
      }))
    };
    res.json(geojson);
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });
});



//dayData
router.get('/dayData', function (req, res, next) {
  console.log(req.query.date); //2023-01-05
  //查询project表中，拍摄时间为req.query.date的数据
  Project.findAll({
    where: {
      //Sequelize.DATE
      time: {
        [Sequelize.Op.between]: [req.query.date + ' 00:00:00', req.query.date + ' 23:59:59']
      }
      
    }
  }).then(function (data) {
    res.json({
      code: 200,
      data: data
    });
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });

});
///monthData?date=2023-01
router.get('/monthData', function (req, res, next) {
  //查询project表中，拍摄时间为req.query.date的数据
  Project.findAll({
    where: {
      time: {
        [Sequelize.Op.between]: [req.query.date + '-01 00:00:00', req.query.date + '-31 23:59:59']
      }
    }
  }).then(function (data) {
    res.json({
      code: 200,
      data: data
    });
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });

});
//yearData?date=2023
router.get('/yearData', function (req, res, next) {
  //查询project表中，拍摄时间为req.query.date的数据
  Project.findAll({
    where: {
      time: {
        [Sequelize.Op.between]: [req.query.date + '-01-01 00:00:00', req.query.date + '-12-31 23:59:59']
      }
    }
  }).then(function (data) {
    res.json({
      code: 200,
      data: data
    });
  }).catch(function (err) {
    console.log(err);
    res.json(err);
  });

});












//图片上传
var multer = require('multer');
var fs = require('fs');
var path = require('path');
var upload = multer({
  dest: 'public/upload/'
});
router.post('/upload', upload.single('file'), function (req, res, next) {
  let file = req.file;
  let extname = path.extname(file.originalname);
  let oldPath = file.path;
  let newPath = file.path + extname;
  fs.rename(oldPath, newPath, function (err) {
    if (err) {
      res.json({
        code: 400,
        data: err
      });
    } else {
      res.json({
        code: 200,
        data: {
          path: newPath
        }
      });
    }
  });
});


module.exports = router;